﻿Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient


''' <summary>
''' Clase de la capa de acceso a datos DAL que implementa el patrón Table Data Gateway para el manejo de la entidad Busqueda
''' </summary>
''' <remarks></remarks>
Public Class TDGBusqueda

    Private Shared m_Instance As TDGBusqueda = Nothing


    Public Function Insert(conn As SqlConnection, Busq_Titulo As String, _
                                                Busq_FCreacion As DateTime, _
                                                Busq_FInicioProg As DateTime, _
                                                Busq_FFinProg As DateTime, _
                                                Busq_Detalle As String, _
                                                Busq_TelPostulaciones As String, _
                                                Busq_MailPostulaciones As String, _
                                                Dep_CodigoSolicitante As Int16, _
                                                Emp_LegajoRespRRHH As Int16, _
                                                Emp_LegajoRespSector As Int16, _
                                                EstB_Codigo As String, _
                                                Optional ByRef Busq_Numero As Int16 = 0) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16

        Dim outParam_Busq_Numero As SqlParameter

        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.addBusqueda"
        command.Parameters.AddWithValue("@Busq_Titulo", Busq_Titulo)
        command.Parameters.AddWithValue("@Busq_FInicioProg", Busq_FInicioProg)
        command.Parameters.AddWithValue("@Busq_FFinProg", Busq_FFinProg)
        command.Parameters.AddWithValue("@Busq_Detalle", Busq_Detalle)
        command.Parameters.AddWithValue("@Busq_TelPostulaciones", Busq_TelPostulaciones)
        command.Parameters.AddWithValue("@Busq_MailPostulaciones", Busq_MailPostulaciones)
        command.Parameters.AddWithValue("@Dep_CodigoSolicitante", Dep_CodigoSolicitante)
        command.Parameters.AddWithValue("@Emp_LegajoRespRRHH", Emp_LegajoRespRRHH)
        command.Parameters.AddWithValue("@Emp_LegajoRespSector", Emp_LegajoRespSector)
        command.Parameters.AddWithValue("@EstB_Codigo", EstB_Codigo)
        command.Parameters.AddWithValue("@DVH", 0)

        outParam_Busq_Numero = command.Parameters.Add("@Busq_Numero", SqlDbType.SmallInt)
        outParam_Busq_Numero.Direction = ParameterDirection.InputOutput


        iRowsAffected = command.ExecuteNonQuery()

        ' Retorno valores
        Busq_Numero = outParam_Busq_Numero.Value


        Return iRowsAffected

    End Function


    Public Function Update(conn As SqlConnection, Busq_Numero As Int16, _
                                                Busq_Titulo As String, _
                                                Busq_FCreacion As DateTime, _
                                                Busq_FInicioProg As DateTime, _
                                                Busq_FFinProg As DateTime, _
                                                Busq_Detalle As String, _
                                                Busq_TelPostulaciones As String, _
                                                Busq_MailPostulaciones As String, _
                                                Dep_CodigoSolicitante As Int16, _
                                                Emp_LegajoRespRRHH As Int16, _
                                                Emp_LegajoRespSector As Int16, _
                                                EstB_Codigo As String) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.updBusqueda"
        command.Parameters.AddWithValue("@Busq_Numero", Busq_Numero)
        command.Parameters.AddWithValue("@Busq_Titulo", Busq_Titulo)
        command.Parameters.AddWithValue("@Busq_FInicioProg", Busq_FInicioProg)
        command.Parameters.AddWithValue("@Busq_FFinProg", Busq_FFinProg)
        command.Parameters.AddWithValue("@Busq_Detalle", Busq_Detalle)
        command.Parameters.AddWithValue("@Busq_TelPostulaciones", Busq_TelPostulaciones)
        command.Parameters.AddWithValue("@Busq_MailPostulaciones", Busq_MailPostulaciones)
        command.Parameters.AddWithValue("@Dep_CodigoSolicitante", Dep_CodigoSolicitante)
        command.Parameters.AddWithValue("@Emp_LegajoRespRRHH", Emp_LegajoRespRRHH)
        command.Parameters.AddWithValue("@Emp_LegajoRespSector", Emp_LegajoRespSector)
        command.Parameters.AddWithValue("@EstB_Codigo", EstB_Codigo)
        command.Parameters.AddWithValue("@DVH", 0)


        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Delete(conn As SqlConnection, Busq_Numero As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16

        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "dbo.delBusqueda"
        command.Parameters.AddWithValue("@Busq_Numero", Busq_Numero)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Finalizar(conn As SqlConnection, Busq_Numero As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16

        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "dbo.updBusqueda_Finalizar"
        command.Parameters.AddWithValue("@Busq_Numero", Busq_Numero)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Suspender(conn As SqlConnection, Busq_Numero As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16

        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "dbo.updBusqueda_Suspender"
        command.Parameters.AddWithValue("@Busq_Numero", Busq_Numero)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Activar(conn As SqlConnection, Busq_Numero As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16

        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "dbo.updBusqueda_Activar"
        command.Parameters.AddWithValue("@Busq_Numero", Busq_Numero)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Find(conn As SqlConnection, Busq_Numero As Int16) As SqlDataReader
        Dim command As New SqlCommand
        Dim dr As SqlDataReader


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.getBusqueda"
        command.Parameters.AddWithValue("@Busq_Numero", Busq_Numero)


        dr = command.ExecuteReader()

        Return dr

    End Function

    Public Function FindAll(conn As SqlConnection) As SqlDataReader
        Dim command As New SqlCommand
        Dim dr As SqlDataReader

        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.getBusqueda_All"


        dr = command.ExecuteReader()

        Return dr

    End Function

    Public Shared ReadOnly Property Instance()
        Get
            If (m_Instance Is Nothing) Then
                m_Instance = New TDGBusqueda()
            End If

            Return m_Instance
        End Get
    End Property

    Private Sub New()

    End Sub

End Class
